<?php defined('SYSPATH') or die('No direct script access.');

class Clubs_Model extends Model {

	protected $temp_filename;
	
	public function __construct() {		
		$this->temp_filename = '';
		$this->db = Database::instance();
	}
	
	public function count_clubs()
    { 
		$query = $this->db->select('id')->get('clubs');
		return $query->count();
    }

	public function count_club_members($id)
    { 
    	$sql = '
			SELECT count(*) as total
			FROM clubs_profiles cp INNER JOIN profiles p ON cp.profiles_id=p.id
			WHERE p.expire_date >= CURDATE() 
			AND p.status=1 
			AND cp.club_id=?    	
    	';
    	
		$result = $this->db->query($sql, array($id))->current()->total;
		return $result;
    }

    public function browse_club_members($id, $limit, $offset)
    {
    	$sql = '
			SELECT cp.club_id, 
			p.id, 
			p.prefix_id, 
			(SELECT title FROM masters WHERE type_id=2 AND id=p.prefix_id) AS prefix,
			p.firstname, 
			p.lastname,
			p.member_type as member_type_id,
			(SELECT title FROM masters WHERE type_id=5 AND id=p.member_type) AS member_type
			FROM clubs_profiles cp INNER JOIN profiles p ON cp.profiles_id=p.id
			WHERE p.expire_date >= CURDATE() 
			AND p.status=1 
			AND cp.club_id=?
			ORDER BY p.firstname, p.lastname
			LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($id, $offset, $limit));
		return $result;
    }
    
 	public function browse_all_members($id)
    {
    	$sql = '
			SELECT cp.club_id, 
			p.id, 
			p.prefix_id, 
			(SELECT title FROM masters WHERE type_id=2 AND id=p.prefix_id) AS prefix,
			p.firstname, 
			p.lastname,
			p.member_type as member_type_id,
			(SELECT title FROM masters WHERE type_id=5 AND id=p.member_type) AS member_type
			FROM clubs_profiles cp INNER JOIN profiles p ON cp.profiles_id=p.id
			WHERE p.expire_date >= CURDATE() 
			AND p.status=1 
			AND cp.club_id=?
			ORDER BY p.firstname, p.lastname
    	';
		$result = $this->db->query($sql, array($id))->result(TRUE);
		return $result;
    }
    
    public function browse($limit, $offset)
    {
    	$sql = '
			SELECT * 
			FROM clubs 
			ORDER BY name
			LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($offset, $limit));
		return $result;
    }
    
    public function loads() {
    	$sql = '
			SELECT * 
			FROM clubs 
			ORDER BY name;
    	';
		$query = $this->db->query($sql);
		return $query->result(FALSE);
    }
    
    public function load($id) {
    	$sql = '
			SELECT * 
			FROM clubs 
			WHERE id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
    }
    
    public function get_image_fileneme($id) {
    	$sql = '
			SELECT logo 
			FROM clubs 
			WHERE id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->current()->logo;
    }
    
    public function save($data, $mode='add') {
		$this->db->query('BEGIN');
		try {
			if ($mode=='add') {
				$sql = 'INSERT INTO clubs(name, description, logo, created) VALUES(?,?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['name'], $data['description'], $data['logo']));
			}
			else {
				if (empty($data['logo'])) {
					$sql = 'UPDATE clubs SET name=?, description=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['name'], $data['description'], $data['id']));
				} 
				else {
					$sql = 'UPDATE clubs SET name=?, description=?, logo=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['name'], $data['description'], $data['logo'], $data['id']));
				} 
				
			}
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }
    
    public function file_validate($file_name) {
	    $_FILES = Validation::factory($_FILES)
					->add_rules('picture', 
								'upload::valid', 
								'upload::type[gif,jpg,png]', 
								'upload::size[1M]');
		 
		if ($_FILES->validate())
		{
			$config = Kohana::config('hsm');
			
			// Temporary file name
			$this->temp_filename = upload::save($file_name);
			if (file_exists($this->temp_filename)) {
				// Resize, sharpen, and save the image
				Image::factory($this->temp_filename)
					->resize($config['image_max_width'], $config['image_max_height'], Image::AUTO)
					->save($config['upload_club'].basename($this->temp_filename));
			 
				// Remove the temporary file
				unlink($this->temp_filename);
				$this->temp_filename = $config['upload_club'].basename($this->temp_filename);
			}
			else {
				$this->temp_filename = '';
			}
		}
		return $_FILES;
    }
    
    public function get_filename() {
    	return $this->temp_filename;
    }
    
    public function delete($id) {
 		$this->db->query('BEGIN');
		try {
			$config = Kohana::config('hsm');
			$filename = $config['upload_club'].basename($this->get_image_fileneme($id));
			
			$sql = 'DELETE FROM clubs WHERE id=?;';
			
			$query = $this->db->query($sql, array($id));
			
			$this->db->query('COMMIT');
			unlink($filename);
			
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
    }
    
	public function delete_member($club_id, $profile_id) {
 		$this->db->query('BEGIN');
		try {
			$sql = 'DELETE FROM clubs_profiles WHERE club_id=? AND profiles_id=?;';
			
			$query = $this->db->query($sql, array($club_id, $profile_id));
			
			$this->db->query('COMMIT');
			
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
    }
}